from flask import Flask, request, render_template, jsonify, make_response
import pandas as pd
from scipy import stats
import os
import mysql.connector
from datetime import datetime
from flask_cors import CORS

app = Flask(__name__)
app.config['UPLOAD_FOLDER'] = 'uploads/'
app.config['ALLOWED_EXTENSIONS'] = {'xlsx', 'xls'}
os.makedirs(app.config['UPLOAD_FOLDER'], exist_ok=True)

db_config = {
    'user': 'root',
    'password': '666666',
    'host': 'localhost',
    'database': 'store',
}


def allowed_file(filename):
    return '.' in filename and filename.rsplit('.', 1)[1].lower() in app.config['ALLOWED_EXTENSIONS']


def record_test_history(test_type, p_value, result):
    conn = mysql.connector.connect(**db_config)
    cursor = conn.cursor()
    test_type_description = {
        'shapiro': '正态分布检验',
        'ttest': '双样本T检验',
        'anova': 'Anova检验'
    }[test_type]
    sql = """
    INSERT INTO test_history (test_type, P_value, result) VALUES (%s, %s, %s)
    """
    cursor.execute(sql, (test_type_description, p_value, result))
    conn.commit()
    cursor.close()
    conn.close()


@app.route('/', methods=['GET', 'POST'])
def upload_file():
    if request.method == 'POST':
        if 'file' not in request.files:
            return jsonify({"error": "No file part"}), 400
        file = request.files['file']
        if file and allowed_file(file.filename):
            try:
                file_path = os.path.join(app.config['UPLOAD_FOLDER'], file.filename)
                file.save(file_path)
                df = pd.read_excel(file_path, sheet_name='Sheet1')
            except Exception as e:
                return jsonify({"error": f"Failed to read file: {str(e)}"}), 500

            test_type = request.form['test_type']

            try:
                if test_type == 'shapiro':
                    data_A = df['A']
                    p_value = stats.shapiro(data_A.dropna())[1]
                    rounded_p_value = float(round(p_value, 3))

                    result = "符合正态分布" if rounded_p_value > 0.05 else "不符合正态分布"

                elif test_type == 'ttest':
                    if 'A' not in df.columns or 'B' not in df.columns:
                        return jsonify({"error": "Excel file must contain columns A and B"}), 400
                    data_A = df['A']
                    data_B = df['B']
                    p_value = stats.ttest_ind(data_A.dropna(), data_B.dropna())[1]
                    rounded_p_value = float(round(p_value, 3))
                    alpha = 0.05
                    result = "两个样本的均值存在显著差异" if rounded_p_value < alpha else "两个样本的均值不存在显著差异"

                elif test_type == 'anova':
                    if 'A' not in df.columns or 'B' not in df.columns or 'C' not in df.columns:
                        return jsonify({"error": "Excel file must contain columns A, B, and C"}), 400
                    data_A = df['A']
                    data_B = df['B']
                    data_C = df['C']
                    f_value, p_value = stats.f_oneway(data_A.dropna(), data_B.dropna(), data_C.dropna())
                    rounded_p_value = float(round(p_value, 3))
                    alpha = 0.05
                    result = "至少两个样本的均值存在显著差异" if rounded_p_value < alpha else "三个样本的均值不存在显著差异"

                else:
                    return jsonify({"error": "Invalid test type"}), 400

                record_test_history(test_type, rounded_p_value, result)
                response_text = f"P值: {rounded_p_value}\n{result}"
                return jsonify({"message": response_text}), 200

            except Exception as e:
                return jsonify({"error": f"Failed to process request: {str(e)}"}), 500

        else:
            return jsonify({"error": "Invalid request method"}), 405

    return render_template('upload.html')  # Assuming upload.html is the correct template name

if __name__ == '__main__':
    app.run(debug=True)